package cn.com.dao;

import cn.com.entity.ClassTimetable;
import cn.com.entity.Clazz;
import cn.com.entity.Course;
import cn.com.entity.Teacher;
import cn.com.tool.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Project:Academic - TaskDao
 *
 * @author 黄俊玮 On Administrator
 * @version 1.0
 * @DataTime 2024/6/15 18:54:21
 * @since 1.8
 */
public class TaskDao {
    public List<ClassTimetable> getClassTimetableList(){

        List<ClassTimetable> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            String sql = "SELECT `t_user`.id AS id,`t_user`.name AS `tname`,`t_task`.cid,`t_class`.name AS cname,`t_task`.coid,`t_courses`.c_name  FROM t_task \n" +
                    "\tright JOIN `t_user` ON `t_task`.tid = `t_user`.id\n" +
                    "\tright JOIN `t_class` ON `t_class`.id = `t_task`.cid\n" +
                    "\tLEFT JOIN `t_courses` ON `t_task`.coid = `t_courses`.c_id " +
                    "WHERE `t_user`.role =2";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String tname = resultSet.getString("tname");
                String cid = resultSet.getString("cid");
                String cname = resultSet.getString("cname");
                int c_id = resultSet.getInt("coid");
                String c_name = resultSet.getString("c_name");

                list.add(new ClassTimetable(id,tname,cid,cname,c_id,c_name));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }

        return list;
    }
    public List<Teacher> getTeacherList(){

        List<Teacher> list =new ArrayList<>();
        Connection connection =null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from `t_user` where role = 2");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = Integer.parseInt(resultSet.getString("id"));
                String name = resultSet.getString("name");
                list.add(new Teacher(id,name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return list;

    }
    public List< Clazz > getClazzList(){
        Connection connection = null;
        PreparedStatement  preparedStatement =null;
        ResultSet resultSet = null;
        List<Clazz> list = new ArrayList<>();
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_class");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                list.add(new Clazz(id,name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
    public List<Course>  getCourseList(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Course> list = new ArrayList<>();
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from `t_courses`");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String id = resultSet.getString("c_id");
                String name = resultSet.getString("c_name");
                list.add(new Course(id,name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return list;
    }
    public void delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("delete from t_task where tid =" + id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }
    }
    public boolean add(String teacher, String aClass, String courses) {
        String sql = "SELECT `t_user`.id AS id,`t_user`.name AS `tname`,`t_task`.cid,`t_class`.name AS cname,`t_task`.coid,`t_courses`.c_name  FROM t_task " +
                "RIGHT JOIN `t_user` ON `t_task`.tid = `t_user`.id " +
                "LEFT JOIN `t_class` ON `t_class`.id = `t_task`.cid " +
                "LEFT JOIN `t_courses` ON `t_task`.coid = `t_courses`.c_id " +
                "WHERE `t_user`.role =2 " +
                "AND `t_user`.id =   " + teacher+
                " AND `t_task`.cid = '"+aClass+"' " +
                "AND `t_task`.coid = "+courses+";";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int i = 0;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Jdbc.close(resultSet,preparedStatement,connection);
                return false;
            } else {
                preparedStatement = connection.prepareStatement("insert into t_task(cid,coid,tid) values('"+aClass+"',"+courses+","+teacher+") ");
                i = preparedStatement.executeUpdate();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return i > 0 ? true:false;
    }
}
